package net.zoneland.knowledge.dao;

import java.util.List;
import net.zoneland.knowledge.model.QaBankExample.Criteria;
import net.zoneland.knowledge.model.QaBankExample.Criterion;
import net.zoneland.knowledge.model.QaBankExample;
import net.zoneland.knowledge.model.QaBankWithBLOBs;
import org.apache.ibatis.jdbc.SQL;

public class QaBankSqlProvider {

    public String countByExample(QaBankExample example) {
        SQL sql = new SQL();
        sql.SELECT("count(*)").FROM("AITK_QA_BANK");
        applyWhere(sql, example, false);
        return sql.toString();
    }

    public String insertSelective(QaBankWithBLOBs record) {
        SQL sql = new SQL();
        sql.INSERT_INTO("AITK_QA_BANK");
        
        if (record.getId() != null) {
            sql.VALUES("ID", "#{id,jdbcType=VARCHAR}");
        }
        
        if (record.getBankId() != null) {
            sql.VALUES("BANKID", "#{bankId,jdbcType=VARCHAR}");
        }
        
        if (record.getCategory() != null) {
            sql.VALUES("CATEGORY", "#{category,jdbcType=VARCHAR}");
        }
        
        if (record.getLevel1Title() != null) {
            sql.VALUES("LEVEL1TITLE", "#{level1Title,jdbcType=VARCHAR}");
        }
        
        if (record.getLevel2Title() != null) {
            sql.VALUES("LEVEL2TITLE", "#{level2Title,jdbcType=VARCHAR}");
        }
        
        if (record.getLevel3Title() != null) {
            sql.VALUES("LEVEL3TITLE", "#{level3Title,jdbcType=VARCHAR}");
        }
        
        if (record.getQuestion() != null) {
            sql.VALUES("QUESTION", "#{question,jdbcType=VARCHAR}");
        }
        
        if (record.getTargetId() != null) {
            sql.VALUES("TARGETID", "#{targetId,jdbcType=VARCHAR}");
        }
        
        if (record.getTargetName() != null) {
            sql.VALUES("TARGETNAME", "#{targetName,jdbcType=VARCHAR}");
        }
        
        if (record.getOrgDn() != null) {
            sql.VALUES("ORGDN", "#{orgDn,jdbcType=VARCHAR}");
        }
        
        if (record.getOrgName() != null) {
            sql.VALUES("ORGNAME", "#{orgName,jdbcType=VARCHAR}");
        }
        
        if (record.getOrgDeptName() != null) {
            sql.VALUES("ORGDEPTNAME", "#{orgDeptName,jdbcType=VARCHAR}");
        }
        
        if (record.getOrgDeptDn() != null) {
            sql.VALUES("ORGDEPTDN", "#{orgDeptDn,jdbcType=VARCHAR}");
        }
        
        if (record.getQaSort() != null) {
            sql.VALUES("QA_SORT", "#{qaSort,jdbcType=DECIMAL}");
        }
        
        if (record.getTags() != null) {
            sql.VALUES("TAGS", "#{tags,jdbcType=VARCHAR}");
        }
        
        if (record.getSearchtext() != null) {
            sql.VALUES("SEARCHTEXT", "#{searchtext,jdbcType=VARCHAR}");
        }
        
        if (record.getExactmatch1() != null) {
            sql.VALUES("EXACTMATCH1", "#{exactmatch1,jdbcType=VARCHAR}");
        }
        
        if (record.getExactmatch2() != null) {
            sql.VALUES("EXACTMATCH2", "#{exactmatch2,jdbcType=VARCHAR}");
        }
        
        if (record.getExactmatch3() != null) {
            sql.VALUES("EXACTMATCH3", "#{exactmatch3,jdbcType=VARCHAR}");
        }
        
        if (record.getDescription() != null) {
            sql.VALUES("DESCRIPTION", "#{description,jdbcType=VARCHAR}");
        }
        
        sql.VALUES("STATUS", "#{status,jdbcType=DECIMAL}");
        
        if (record.getAnsContentName() != null) {
            sql.VALUES("ANSCONTENTNAME", "#{ansContentName,jdbcType=VARCHAR}");
        }
        
        if (record.getCreatetime() != null) {
            sql.VALUES("CREATETIME", "#{createtime,jdbcType=TIMESTAMP}");
        }
        
        if (record.getUpdatetime() != null) {
            sql.VALUES("UPDATETIME", "#{updatetime,jdbcType=TIMESTAMP}");
        }
        
        if (record.getCreator() != null) {
            sql.VALUES("CREATOR", "#{creator,jdbcType=VARCHAR}");
        }
        
        if (record.getUpdater() != null) {
            sql.VALUES("UPDATER", "#{updater,jdbcType=VARCHAR}");
        }
        
        if (record.getUserids() != null) {
            sql.VALUES("USERIDS", "#{userids,jdbcType=VARCHAR}");
        }
        
        if (record.getUsernames() != null) {
            sql.VALUES("USERNAMES", "#{usernames,jdbcType=VARCHAR}");
        }
        
        if (record.getDeptids() != null) {
            sql.VALUES("DEPTIDS", "#{deptids,jdbcType=VARCHAR}");
        }
        
        if (record.getDeptnames() != null) {
            sql.VALUES("DEPTNAMES", "#{deptnames,jdbcType=VARCHAR}");
        }
        
        if (record.getRoleflags() != null) {
            sql.VALUES("ROLEFLAGS", "#{roleflags,jdbcType=VARCHAR}");
        }
        
        if (record.getShowrichtext() != null) {
            sql.VALUES("SHOWRICHTEXT", "#{showrichtext,jdbcType=DECIMAL}");
        }
        
        if (record.getSamerichtext() != null) {
            sql.VALUES("SAMERICHTEXT", "#{samerichtext,jdbcType=DECIMAL}");
        }
        
        if (record.getAnsContent() != null) {
            sql.VALUES("ANSCONTENT", "#{ansContent,jdbcType=CLOB}");
        }
        
        if (record.getAnsContentFile() != null) {
            sql.VALUES("ANSCONTENTFILE", "#{ansContentFile,jdbcType=BLOB}");
        }
        
        if (record.getCustomizedinfo() != null) {
            sql.VALUES("CUSTOMIZEDINFO", "#{customizedinfo,jdbcType=CLOB}");
        }
        
        if (record.getPcRichText() != null) {
            sql.VALUES("PCRICHTEXT", "#{pcRichText,jdbcType=CLOB}");
        }
        
        if (record.getMoaRichText() != null) {
            sql.VALUES("MOARICHTEXT", "#{moaRichText,jdbcType=CLOB}");
        }
        
        return sql.toString();
    }

    public String selectByExampleWithBLOBs(QaBankExample example) {
        SQL sql = new SQL();
        if (example != null && example.isDistinct()) {
            sql.SELECT_DISTINCT("ID");
        } else {
            sql.SELECT("ID");
        }
        sql.SELECT("BANKID");
        sql.SELECT("CATEGORY");
        sql.SELECT("LEVEL1TITLE");
        sql.SELECT("LEVEL2TITLE");
        sql.SELECT("LEVEL3TITLE");
        sql.SELECT("QUESTION");
        sql.SELECT("TARGETID");
        sql.SELECT("TARGETNAME");
        sql.SELECT("ORGDN");
        sql.SELECT("ORGNAME");
        sql.SELECT("ORGDEPTNAME");
        sql.SELECT("ORGDEPTDN");
        sql.SELECT("QA_SORT");
        sql.SELECT("TAGS");
        sql.SELECT("SEARCHTEXT");
        sql.SELECT("EXACTMATCH1");
        sql.SELECT("EXACTMATCH2");
        sql.SELECT("EXACTMATCH3");
        sql.SELECT("DESCRIPTION");
        sql.SELECT("STATUS");
        sql.SELECT("ANSCONTENTNAME");
        sql.SELECT("CREATETIME");
        sql.SELECT("UPDATETIME");
        sql.SELECT("CREATOR");
        sql.SELECT("UPDATER");
        sql.SELECT("USERIDS");
        sql.SELECT("USERNAMES");
        sql.SELECT("DEPTIDS");
        sql.SELECT("DEPTNAMES");
        sql.SELECT("ROLEFLAGS");
        sql.SELECT("SHOWRICHTEXT");
        sql.SELECT("SAMERICHTEXT");
        sql.SELECT("ANSCONTENT");
        sql.SELECT("ANSCONTENTFILE");
        sql.SELECT("CUSTOMIZEDINFO");
        sql.SELECT("PCRICHTEXT");
        sql.SELECT("MOARICHTEXT");
        sql.FROM("AITK_QA_BANK");
        applyWhere(sql, example, false);
        
        if (example != null && example.getOrderByClause() != null) {
            sql.ORDER_BY(example.getOrderByClause());
        }
        
        return sql.toString();
    }

    public String selectByExample(QaBankExample example) {
        SQL sql = new SQL();
        if (example != null && example.isDistinct()) {
            sql.SELECT_DISTINCT("ID");
        } else {
            sql.SELECT("ID");
        }
        sql.SELECT("BANKID");
        sql.SELECT("CATEGORY");
        sql.SELECT("LEVEL1TITLE");
        sql.SELECT("LEVEL2TITLE");
        sql.SELECT("LEVEL3TITLE");
        sql.SELECT("QUESTION");
        sql.SELECT("TARGETID");
        sql.SELECT("TARGETNAME");
        sql.SELECT("ORGDN");
        sql.SELECT("ORGNAME");
        sql.SELECT("ORGDEPTNAME");
        sql.SELECT("ORGDEPTDN");
        sql.SELECT("QA_SORT");
        sql.SELECT("TAGS");
        sql.SELECT("SEARCHTEXT");
        sql.SELECT("EXACTMATCH1");
        sql.SELECT("EXACTMATCH2");
        sql.SELECT("EXACTMATCH3");
        sql.SELECT("DESCRIPTION");
        sql.SELECT("STATUS");
        sql.SELECT("ANSCONTENTNAME");
        sql.SELECT("CREATETIME");
        sql.SELECT("UPDATETIME");
        sql.SELECT("CREATOR");
        sql.SELECT("UPDATER");
        sql.SELECT("USERIDS");
        sql.SELECT("USERNAMES");
        sql.SELECT("DEPTIDS");
        sql.SELECT("DEPTNAMES");
        sql.SELECT("ROLEFLAGS");
        sql.SELECT("SHOWRICHTEXT");
        sql.SELECT("SAMERICHTEXT");
        sql.FROM("AITK_QA_BANK");
        applyWhere(sql, example, false);
        
        if (example != null && example.getOrderByClause() != null) {
            sql.ORDER_BY(example.getOrderByClause());
        }
        
        return sql.toString();
    }

    public String updateByPrimaryKeySelective(QaBankWithBLOBs record) {
        SQL sql = new SQL();
        sql.UPDATE("AITK_QA_BANK");
        
        if (record.getBankId() != null) {
            sql.SET("BANKID = #{bankId,jdbcType=VARCHAR}");
        }
        
        if (record.getCategory() != null) {
            sql.SET("CATEGORY = #{category,jdbcType=VARCHAR}");
        }
        
        if (record.getLevel1Title() != null) {
            sql.SET("LEVEL1TITLE = #{level1Title,jdbcType=VARCHAR}");
        }
        
        if (record.getLevel2Title() != null) {
            sql.SET("LEVEL2TITLE = #{level2Title,jdbcType=VARCHAR}");
        }
        
        if (record.getLevel3Title() != null) {
            sql.SET("LEVEL3TITLE = #{level3Title,jdbcType=VARCHAR}");
        }
        
        if (record.getQuestion() != null) {
            sql.SET("QUESTION = #{question,jdbcType=VARCHAR}");
        }
        
        if (record.getTargetId() != null) {
            sql.SET("TARGETID = #{targetId,jdbcType=VARCHAR}");
        }
        
        if (record.getTargetName() != null) {
            sql.SET("TARGETNAME = #{targetName,jdbcType=VARCHAR}");
        }
        
        if (record.getOrgDn() != null) {
            sql.SET("ORGDN = #{orgDn,jdbcType=VARCHAR}");
        }
        
        if (record.getOrgName() != null) {
            sql.SET("ORGNAME = #{orgName,jdbcType=VARCHAR}");
        }
        
        if (record.getOrgDeptName() != null) {
            sql.SET("ORGDEPTNAME = #{orgDeptName,jdbcType=VARCHAR}");
        }
        
        if (record.getOrgDeptDn() != null) {
            sql.SET("ORGDEPTDN = #{orgDeptDn,jdbcType=VARCHAR}");
        }
        
        if (record.getQaSort() != null) {
            sql.SET("QA_SORT = #{qaSort,jdbcType=DECIMAL}");
        }
        
        if (record.getTags() != null) {
            sql.SET("TAGS = #{tags,jdbcType=VARCHAR}");
        }
        
        if (record.getSearchtext() != null) {
            sql.SET("SEARCHTEXT = #{searchtext,jdbcType=VARCHAR}");
        }
        
        if (record.getExactmatch1() != null) {
            sql.SET("EXACTMATCH1 = #{exactmatch1,jdbcType=VARCHAR}");
        }
        
        if (record.getExactmatch2() != null) {
            sql.SET("EXACTMATCH2 = #{exactmatch2,jdbcType=VARCHAR}");
        }
        
        if (record.getExactmatch3() != null) {
            sql.SET("EXACTMATCH3 = #{exactmatch3,jdbcType=VARCHAR}");
        }
        
        if (record.getDescription() != null) {
            sql.SET("DESCRIPTION = #{description,jdbcType=VARCHAR}");
        }
        
        sql.SET("STATUS = #{status,jdbcType=DECIMAL}");
        
        if (record.getAnsContentName() != null) {
            sql.SET("ANSCONTENTNAME = #{ansContentName,jdbcType=VARCHAR}");
        }
        
        if (record.getCreatetime() != null) {
            sql.SET("CREATETIME = #{createtime,jdbcType=TIMESTAMP}");
        }
        
        if (record.getUpdatetime() != null) {
            sql.SET("UPDATETIME = #{updatetime,jdbcType=TIMESTAMP}");
        }
        
        if (record.getCreator() != null) {
            sql.SET("CREATOR = #{creator,jdbcType=VARCHAR}");
        }
        
        if (record.getUpdater() != null) {
            sql.SET("UPDATER = #{updater,jdbcType=VARCHAR}");
        }
        
        if (record.getUserids() != null) {
            sql.SET("USERIDS = #{userids,jdbcType=VARCHAR}");
        }
        
        if (record.getUsernames() != null) {
            sql.SET("USERNAMES = #{usernames,jdbcType=VARCHAR}");
        }
        
        if (record.getDeptids() != null) {
            sql.SET("DEPTIDS = #{deptids,jdbcType=VARCHAR}");
        }
        
        if (record.getDeptnames() != null) {
            sql.SET("DEPTNAMES = #{deptnames,jdbcType=VARCHAR}");
        }
        
        if (record.getRoleflags() != null) {
            sql.SET("ROLEFLAGS = #{roleflags,jdbcType=VARCHAR}");
        }
        
        if (record.getShowrichtext() != null) {
            sql.SET("SHOWRICHTEXT = #{showrichtext,jdbcType=DECIMAL}");
        }
        
        if (record.getSamerichtext() != null) {
            sql.SET("SAMERICHTEXT = #{samerichtext,jdbcType=DECIMAL}");
        }
        
        if (record.getAnsContent() != null) {
            sql.SET("ANSCONTENT = #{ansContent,jdbcType=CLOB}");
        }
        
        if (record.getAnsContentFile() != null) {
            sql.SET("ANSCONTENTFILE = #{ansContentFile,jdbcType=BLOB}");
        }
        
        if (record.getCustomizedinfo() != null) {
            sql.SET("CUSTOMIZEDINFO = #{customizedinfo,jdbcType=CLOB}");
        }
        
        if (record.getPcRichText() != null) {
            sql.SET("PCRICHTEXT = #{pcRichText,jdbcType=CLOB}");
        }
        
        if (record.getMoaRichText() != null) {
            sql.SET("MOARICHTEXT = #{moaRichText,jdbcType=CLOB}");
        }
        
        sql.WHERE("ID = #{id,jdbcType=VARCHAR}");
        
        return sql.toString();
    }

    protected void applyWhere(SQL sql, QaBankExample example, boolean includeExamplePhrase) {
        if (example == null) {
            return;
        }
        
        String parmPhrase1;
        String parmPhrase1_th;
        String parmPhrase2;
        String parmPhrase2_th;
        String parmPhrase3;
        String parmPhrase3_th;
        if (includeExamplePhrase) {
            parmPhrase1 = "%s #{example.oredCriteria[%d].allCriteria[%d].value}";
            parmPhrase1_th = "%s #{example.oredCriteria[%d].allCriteria[%d].value,typeHandler=%s}";
            parmPhrase2 = "%s #{example.oredCriteria[%d].allCriteria[%d].value} and #{example.oredCriteria[%d].criteria[%d].secondValue}";
            parmPhrase2_th = "%s #{example.oredCriteria[%d].allCriteria[%d].value,typeHandler=%s} and #{example.oredCriteria[%d].criteria[%d].secondValue,typeHandler=%s}";
            parmPhrase3 = "#{example.oredCriteria[%d].allCriteria[%d].value[%d]}";
            parmPhrase3_th = "#{example.oredCriteria[%d].allCriteria[%d].value[%d],typeHandler=%s}";
        } else {
            parmPhrase1 = "%s #{oredCriteria[%d].allCriteria[%d].value}";
            parmPhrase1_th = "%s #{oredCriteria[%d].allCriteria[%d].value,typeHandler=%s}";
            parmPhrase2 = "%s #{oredCriteria[%d].allCriteria[%d].value} and #{oredCriteria[%d].criteria[%d].secondValue}";
            parmPhrase2_th = "%s #{oredCriteria[%d].allCriteria[%d].value,typeHandler=%s} and #{oredCriteria[%d].criteria[%d].secondValue,typeHandler=%s}";
            parmPhrase3 = "#{oredCriteria[%d].allCriteria[%d].value[%d]}";
            parmPhrase3_th = "#{oredCriteria[%d].allCriteria[%d].value[%d],typeHandler=%s}";
        }
        
        StringBuilder sb = new StringBuilder();
        List<Criteria> oredCriteria = example.getOredCriteria();
        boolean firstCriteria = true;
        for (int i = 0; i < oredCriteria.size(); i++) {
            Criteria criteria = oredCriteria.get(i);
            if (criteria.isValid()) {
                if (firstCriteria) {
                    firstCriteria = false;
                } else {
                    sb.append(" or ");
                }
                
                sb.append('(');
                List<Criterion> criterions = criteria.getAllCriteria();
                boolean firstCriterion = true;
                for (int j = 0; j < criterions.size(); j++) {
                    Criterion criterion = criterions.get(j);
                    if (firstCriterion) {
                        firstCriterion = false;
                    } else {
                        sb.append(" and ");
                    }
                    
                    if (criterion.isNoValue()) {
                        sb.append(criterion.getCondition());
                    } else if (criterion.isSingleValue()) {
                        if (criterion.getTypeHandler() == null) {
                            sb.append(String.format(parmPhrase1, criterion.getCondition(), i, j));
                        } else {
                            sb.append(String.format(parmPhrase1_th, criterion.getCondition(), i, j,criterion.getTypeHandler()));
                        }
                    } else if (criterion.isBetweenValue()) {
                        if (criterion.getTypeHandler() == null) {
                            sb.append(String.format(parmPhrase2, criterion.getCondition(), i, j, i, j));
                        } else {
                            sb.append(String.format(parmPhrase2_th, criterion.getCondition(), i, j, criterion.getTypeHandler(), i, j, criterion.getTypeHandler()));
                        }
                    } else if (criterion.isListValue()) {
                        sb.append(criterion.getCondition());
                        sb.append(" (");
                        List<?> listItems = (List<?>) criterion.getValue();
                        boolean comma = false;
                        for (int k = 0; k < listItems.size(); k++) {
                            if (comma) {
                                sb.append(", ");
                            } else {
                                comma = true;
                            }
                            if (criterion.getTypeHandler() == null) {
                                sb.append(String.format(parmPhrase3, i, j, k));
                            } else {
                                sb.append(String.format(parmPhrase3_th, i, j, k, criterion.getTypeHandler()));
                            }
                        }
                        sb.append(')');
                    }
                }
                sb.append(')');
            }
        }
        
        if (sb.length() > 0) {
            sql.WHERE(sb.toString());
        }
    }
}